package admission.ecnu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.sun.org.apache.bcel.internal.generic.NEW;

import admission.ecnu.db.DBConnection;
import admission.ecnu.object.OClassInfo;
import admission.ecnu.po.User;
import admission.ecnu.service.inputClassInfo;

public class classInfoDao {
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	
	public classInfoDao() {
		// TODO Auto-generated constructor stub
		this.conn = DBConnection.getConnection();
	}
	
	public OClassInfo classSelectById(String course_id){
		OClassInfo objClass = new OClassInfo();
		try {
			this.pstmt = this.conn
					.prepareStatement("select course_id,course_name,classday,teacher_id,course_type,grade,time,place,stu_num, is_test from course_info where course_id=?");
			this.pstmt.setString(1, course_id);
			this.rs = this.pstmt.executeQuery();
			
			objClass.setClassId(rs.getString("course_id"));
			objClass.setClassName(rs.getString("course_name"));
			objClass.setClassDay(String.valueOf(rs.getInt("classday")));
			objClass.setTeacher(rs.getString("teacher_id"));
			objClass.setType(rs.getString("course_type"));
			objClass.setGrade(rs.getString("grade"));
			objClass.setClassTime(rs.getString("time"));
			objClass.setClassroom(rs.getString("place"));
			objClass.setStudentNum(rs.getString("stu_num"));
			objClass.setIsExam(rs.getString("is_test"));

		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				this.rs.close();
				this.pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);
			
		}
		return objClass;
	}
	//考场，任课老师
	
	public Map classGetOthersById(String course_id) {
		Map Others = new HashMap<String, String>();
		try {
			this.pstmt = this.conn.prepareStatement("select * from course_info where course_id=?");
			this.pstmt.setString(1, course_id);
			this.rs = this.pstmt.executeQuery();

			rs.next();
			Others.put("classroom", rs.getString("place")) ;
			Others.put("course_name", rs.getString("course_name")) ;
			Others.put("teacher", new teacherInfoDao().teacherGetNameById(rs.getString("teacher_id"))) ;
			Others.put("grade", rs.getString("grade")) ;

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				this.rs.close();
				this.pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);

		}
		return Others;
	}
	
	public List<OClassInfo> classSelect(){
		List<OClassInfo> list = new ArrayList<OClassInfo>();
		try {
			this.pstmt = this.conn.prepareStatement("select * from course_info");
			this.rs = this.pstmt.executeQuery();
			while(this.rs.next()){
				OClassInfo objClass = new OClassInfo();
				objClass.setClassId(rs.getString("course_id"));
				objClass.setClassName(rs.getString("course_name"));
				objClass.setClassDay(String.valueOf(rs.getInt("classday")));
				objClass.setTeacher(rs.getString("teacher_id"));
				objClass.setType(rs.getString("course_type"));
				objClass.setGrade(rs.getString("grade"));
				objClass.setClassTime(rs.getString("time"));
				objClass.setClassroom(rs.getString("place"));
				objClass.setStudentNum(rs.getString("stu_num"));
				objClass.setIsExam(rs.getString("is_test"));
				list.add(objClass);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				this.rs.close();
				this.pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);
		}
		return list;
	}
	
	public int classInsert(String username,String userpass){
		int i = 0;
		try {
			this.pstmt = this.conn.prepareStatement("insert into t_user(username,userpass) value(?,?)");
			this.pstmt.setString(1,username);
			this.pstmt.setString(2,userpass);
			i = this.pstmt.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try {
				this.pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);
		}
		return i;
	}
	
	public int classInsert(OClassInfo newClass){
		int i = 0;
		try {
			this.pstmt = this.conn.prepareStatement("insert into course_info(course_id,course_name,classday,teacher_id,course_type,grade,time,place,stu_num,is_test) values(?,?,?,?,?,?,?,?,?,?)");
			this.pstmt.setString(1, newClass.getClassId());
			this.pstmt.setString(2, newClass.getCurriculum());
			this.pstmt.setInt(3, Integer.valueOf(newClass.getClassDay()));
			this.pstmt.setString(4, new teacherInfoDao().getIdByName(newClass.getTeacher()));
			this.pstmt.setString(5, newClass.getType());
			this.pstmt.setString(6, newClass.getGrade());
			this.pstmt.setString(7, newClass.getClassTime());
			this.pstmt.setString(8, newClass.getClassroom());
			this.pstmt.setString(9, newClass.getStudentNum());
			this.pstmt.setString(10, newClass.getIsExam());
			
			i = this.pstmt.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try {
				this.pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);
		}
		return i;
	}
	
	public void classInsert(List classList, Map teacherMap){
		
		
		OClassInfo newClass = new OClassInfo();
		try {
			this.pstmt = this.conn.prepareStatement("insert into course_info(course_id,course_name,classday,teacher_id,course_type,grade,time,place,stu_num,is_test) values(?,?,?,?,?,?,?,?,?,?)");
			for(int j = 0; j<classList.size(); j++){
				
				newClass = (OClassInfo) classList.get(j);
				
				String teacherName = newClass.getTeacher().trim();
				if(teacherMap.get(teacherName)==null){
					System.out.print(teacherName+"\n");
					continue;
				}
				
				this.pstmt.setString(1, newClass.getClassId());
				this.pstmt.setString(2, newClass.getCurriculum());
				this.pstmt.setInt(3, Integer.valueOf(newClass.getClassDay()));
				
					
				this.pstmt.setString(4, (String) teacherMap.get(teacherName));
				this.pstmt.setString(5, newClass.getType());
				this.pstmt.setString(6, newClass.getGrade());
				this.pstmt.setString(7, newClass.getClassTime());
				this.pstmt.setString(8, newClass.getClassroom());
				this.pstmt.setString(9, newClass.getStudentNum());
				this.pstmt.setString(10, newClass.getIsExam());
				this.pstmt.addBatch();
			}
			
			 this.pstmt.executeBatch();
		} catch (Exception e) {
			// TODO: handle exception
			System.out.print(e.toString());
			e.printStackTrace();
		}finally{
			try {
				this.pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			DBConnection.closeConnection(this.conn);
		}
	}
	
	public int classDelete(String id){
		int i = 0;
		try {
			this.pstmt = this.conn.prepareStatement("delete from course_info where id in(?)");
			this.pstmt.setString(1, id);
			i = this.pstmt.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try {
				this.pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);
		}
		return i;
	}
	
	public int classDelete(){
		int i = 0;
		try {
			this.pstmt = this.conn.prepareStatement("delete from course_info");
			i = this.pstmt.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try {
				this.pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);
		}
		return i;
	}
	
	public int classUpdate(String id,String username,String userpass){
		int i = 0;
		try {
			this.pstmt = this.conn.prepareStatement("update t_user set username=?,userpass=? where id=?");
			this.pstmt.setString(1,	username);
			this.pstmt.setString(2, userpass);
			this.pstmt.setString(3, id);
			i = this.pstmt.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try {
				this.pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);
		}
		return i;
	}
	public int classUpdate(OClassInfo newClass){
		int i = 0;
		try {
			this.pstmt = this.conn.prepareStatement("update course_info set course_name=?,teacher=?,course_type=?,grade=?,time=?,place=?,stu_num=?,is_test=? where course_id=?");
			this.pstmt.setString(1, newClass.getCurriculum());
			this.pstmt.setString(2, newClass.getTeacher());
			this.pstmt.setString(3, newClass.getType());
			this.pstmt.setString(4, newClass.getGrade());
			this.pstmt.setString(5, newClass.getClassTime());
			this.pstmt.setString(6, newClass.getClassroom());
			this.pstmt.setString(7, newClass.getStudentNum());
			this.pstmt.setString(8, newClass.getIsExam());
			this.pstmt.setString(9, newClass.getClassId());
			i = this.pstmt.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try {
				this.pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);
		}
		return i;
	}
	
	public void classUpdate(List<OClassInfo> classList){
		for(int i = 0; i<classList.size(); i++){
			classUpdate(classList.get(i));
		}
	}
	
	public List<User> classUpdateLoad(String id){
		List<User> list = new ArrayList<User>();
		try {
			this.pstmt = this.conn.prepareStatement("select username,userpass from t_user where id = ?");
			this.pstmt.setString(1, id);
			this.rs = this.pstmt.executeQuery();
			while(this.rs.next()){
				User user = new User();
				user.setUsername(rs.getString("username"));
				user.setUserpass(rs.getString("userpass"));
				list.add(user);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try {
				this.pstmt.close();
				this.rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);
		}
		return list;
	}
}